create table test_hash(val int, id int);
create table temp(val int, id int);
insert into temp SELECT (random()*2147483647)::int, (random()*2147483647)::int from generate_series(1,10000);
CREATE OR REPLACE FUNCTION insert(k int) RETURNS void as $$  
DECLARE
BEGIN
    FOR I IN 1..k LOOP
        insert into test_btree select * from temp;
    END LOOP;
END
$$
LANGUAGE plpgsql;

create index test_hash_idx on test_hash using hash(val);
create table query2  AS select  val from temp limit 1000;
explain analyze select * from test_hash, query2 where test_hash.val = query2.val; 
select * from test_hash where val in (select (random()*10000)::int from generate_series(1,1000));


create table test_btree(val int, id int);
insert into test_btree SELECT (random()*10000)::int, (random()*10000)::int from generate_series(1,1000000);
create index test_btree_idx on test_btree using btree(val);
explain analyze select * from test_btree, query2 where test_btree.val = query2.val;
select * from test_btree where val in (select (random()*2147483647)::int from generate_series(1,1000));


create table test_gin(val int, id int);
insert into test_gin SELECT (random()*2147483647)::int, (random()*2147483647)::int from generate_series(1,1000000);
create index test_gin_idx on test_gin using gin(val);
select * from test_gin where val in (select (random()*2147483647)::int from generate_series(1,1000));


create EXTENSION hsp;
create table test_gin(val uint4, id int);
create table temp(val int, id int);
insert into temp SELECT (random()*2147483647)::int, (random()*2147483647)::int from generate_series(1,10000);
CREATE OR REPLACE FUNCTION insert(k int) RETURNS void as $$  
DECLARE
BEGIN
    FOR I IN 1..k LOOP
        insert into test_gin select * from temp;
    END LOOP;
END
$$
LANGUAGE plpgsql;
select insert(100);
create index test_gin_idx on test_gin using gin(val);
-- select * from test_gin where val = 1;
create table query  AS select  val from test_gin limit 1000;
explain analyze select * from test_gin, query where test_gin.val = query.val; 
insert into test_gin SELECT (random()*10000)::int, (random()*10000)::int from generate_series(1,1000000);

explain analyze select * from test_gin, query where test_gin.val = query.val; 
select * from test_gin where val in (select (random()*10000)::int::uint4 from generate_series(1,1000));


CREATE EXTENSION hsp;
CREATE EXTENSION hstore;

\i sift1m.sql

SELECT pg_catalog.set_config('search_path', '"$user",public',false);

SET client_min_messages = notice;

SELECT set_m(3);

SELECT create_index('test_hsp','val');

create table query as select code from hm_index1 limit 1000;
\timing on 
drop index hm_index1_code_idx;
create index on hm_index1 using gin(code);
select * from hm_index1, query where hm_index1.code = query.code;


create table test_hash(val int, id int);
insert into test_hash SELECT (random()*2147483647)::int, (random()*2147483647)::int from generate_series(1,1000000);
create index test_hash_idx on test_hash using hash(val);
create table query1  AS select  val from test_hash limit 1;
create table query2  AS select  val from test_hash limit 10;
create table query3  AS select  val from test_hash limit 100;
create table query4  AS select  val from test_hash limit 1000;
explain analyze select * from test_hash, query1 where test_hash.val = query1.val; 
